In modem relational database management systems (RDBMS), the overhead associated with processing client requests can be troublesome. Cache and buffer overflow, I/O bottlenecks, wasted CPU cycle time, shared memory latch contention, network throughput, and other performance side effects often result from poor planning and untested design.
To avoid these and other by-products of a poorly designed system, a client/server DBMS architecture could benefit greatly from a streamlined database statement processing system. In a typical two-tier DBMS architecture, a client issues a database statement (hereinafter illustratively referred to as a “SQL statement”) to a process running on the database server through a proprietary or open-system call level interface (CLI). The server expends a great deal of its run-time resources in parsing the request, creating an execution tree, semantically analyzing the statement, and determining an optimal execution plan. These steps together constitute the compilation, or “hard parse,” steps needed to store and create a “cursor” in cache memory before the server can effectively carry out a client request or return a result set. A cursor is a handle to a query execution area, e.g., an area in memory in which a parsed SQL statement and other information for processing the SQL statement is stored.
Database systems frequently invoke an optimizer to handle the task of creating an execution plan that is optimized to a particular SQL statement. For the most part, optimizing a SQL statement is a manageable task; however, SQL statements written with “bind” variables often present a challenge to the optimizer because the optimal execution plan for such a statement will likely differ from one bind value to another. A bind variable is a placeholder, e.g., for literal values, in a query sent to a server. Previous approaches to optimizing SQL statement execution plans in the face of one or more bind variables written into the statement involved providing the optimizer with a suggestion embedded in the command-line, or rewriting the statement with constants in place of bind variables. The latter approach is undesirable because it suggests defeat of the purpose for using bind variables in the first place, while the former technique requires that a developer or user have pre-existing knowledge of the optimizer's functionality—neither approach is very effective.
The methods and systems for database statement execution plan optimization minimize or eliminate the inherent limitations and drawbacks of current optimization techniques by permitting a database statement issued from a client to be optimized based on one or more bind values.
In one embodiment, the methods and systems for database statement execution plan optimization comprise a frame allocator for allocating space in memory for executing a SQL statement execution plan and a bind value analyzer for peeking at the value of one or more bind variables. A type checker, optimizer, and parser are also provided for type checking, parsing, and optimizing a SQL statement, respectively.
The methods and systems for database statement execution plan optimization offer several advantages, including: enhanced SQL processing performance without costly code revision, better execution plan optimization that utilizes actual bind variable data, and improved processing efficiency for SQL statements with one or more bind variables.
Further details of aspects, objects, and advantages of the invention are described in the detailed description, drawings, and claims.